package com.jd.dict.helper;

import com.jd.dict.dao.BaseDao;
import com.jd.dict.model.TableFieldInfoModel;
import com.jd.dict.model.TableIndexInfoModel;
import com.jd.dict.model.TableInfoModel;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by liuhaiming on 2017/7/28.
 */
public class DaoHelper {
    public static List<TableInfoModel> findAllTableInfo() throws Exception {
        List<TableInfoModel> tableList = new ArrayList<TableInfoModel>();
        TableInfoModel table;

        BaseDao dao = new BaseDao();
        DatabaseMetaData metaData = dao.getConn().getMetaData();

        ResultSet rs = metaData.getTables(null, "%","%",new String[]{"TABLE"});
        List<String> tableNameList = new ArrayList<String>();
        while( rs.next() ) {
            tableNameList.add(rs.getString("TABLE_NAME"));
        }

        for ( String tableName : tableNameList ) {
            System.out.println("============ start create table: " + tableName);

            table = new TableInfoModel();
            table.setTableName(tableName);

            String primaryKey = "";
            ResultSet primaryKeyResultSet = metaData.getPrimaryKeys(dao.getConn().getCatalog(), null, tableName);
            while(primaryKeyResultSet.next()){
                primaryKey = primaryKeyResultSet.getString("COLUMN_NAME");
            }

            if (tableName.indexOf(" ") >= 0) {
                continue;
            }

            if (tableName.indexOf("!") >= 0) {
                continue;
            }

            rs = dao.queryBySql("SHOW CREATE TABLE " + tableName);
            if (rs != null && rs.next()) {
                String create = rs.getString(2);
                String comment = parse(create);
                table.setComment(comment.replace("\\n", "_").replace("/", "_"));
            }

            table.setFieldList( findAllColumnByTable(dao, tableName, primaryKey) );

            // index info create
            table.setIndexList( findAllIndexByTable(dao, tableName) );

            tableList.add(table);
            System.out.println("============ end create table: " + tableName);
        }

        return tableList;
    }

    private static List<TableFieldInfoModel> findAllColumnByTable(BaseDao dao, String tableName, String primaryKey) throws Exception {
        List<TableFieldInfoModel> fieldList = new ArrayList<TableFieldInfoModel>();
        TableFieldInfoModel field = null;

        DatabaseMetaData metaData = dao.getConn().getMetaData();
        ResultSet rs = metaData.getColumns(null, getSchema(dao.getConn()),tableName.toUpperCase(), "%");

        while(rs.next()){
            field = new TableFieldInfoModel();

            String colName = rs.getString("COLUMN_NAME");
            field.setKey(colName);

            field.setPrimaryKey( primaryKey.equals(colName) );

            int isNullable = rs.getInt("NULLABLE");
            field.setNullable(isNullable == 0 ? false : true);

            int colSize = rs.getInt("COLUMN_SIZE");
            field.setFieldLength(colSize);

            String remarks = rs.getString("REMARKS");
            if(remarks == null || remarks.equals("")){
                remarks = colName;
            }
            field.setComment(remarks);

            String type = rs.getString("TYPE_NAME");
            field.setType(type);

            int powerLen = rs.getInt("DECIMAL_DIGITS");
            field.setPowerLength(powerLen);

            fieldList.add(field);
        }

        return fieldList;
    }

    private static List<TableIndexInfoModel> findAllIndexByTable(BaseDao dao, String tableName) throws Exception {
        DatabaseMetaData metaData = dao.getConn().getMetaData();
        ResultSet rs = dao.queryBySql("show index from " + tableName);

        Map<String, TableIndexInfoModel> tableIndexInfoModelMap = new HashMap<String, TableIndexInfoModel>();
        TableIndexInfoModel tmpIndexModel = null;
        while(rs.next()){
            String keyName = rs.getString("Key_name");
            String columnName = rs.getString("Column_name");

            if ( tableIndexInfoModelMap.containsKey(keyName) ) {
                tmpIndexModel = tableIndexInfoModelMap.get(keyName);
            } else {
                tmpIndexModel = new TableIndexInfoModel();
                tmpIndexModel.setKey(keyName);
                tmpIndexModel.setField(new ArrayList<String>());
            }
            tmpIndexModel.getField().add(columnName);

            tableIndexInfoModelMap.put(keyName, tmpIndexModel);
        }

        List<TableIndexInfoModel> tableIndexInfoModels = new ArrayList<TableIndexInfoModel>();
        for (TableIndexInfoModel tableIndexInfoModel : tableIndexInfoModelMap.values()) {
            tableIndexInfoModels.add(tableIndexInfoModel);
        }

        return tableIndexInfoModels;
    }

    //其他数据库不需要这个方法 oracle和db2需要
    private static String getSchema(Connection conn) throws Exception {
        String schema;
        schema = conn.getMetaData().getUserName();
        if ((schema == null) || (schema.length() == 0)) {
            throw new Exception("ORACLE数据库模式不允许为空");
        }
        return schema.toUpperCase().toString();

        }

    public static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        try {
            comment = new String(comment.getBytes("utf-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return comment;
    }
}
